package com.anolesoft.secondaryCostAccounting.mapper;

import cn.hutool.core.util.StrUtil;
import com.anolesoft.secondaryCostAccounting.dto.*;
import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.jdbc.SQL;

import java.util.List;

@Mapper
public interface MatchServiceMapper {
    //查询
    @SelectProvider(type = createSQL.class, method = "queryMatchInfoSQL")
    List<MatchDO> queryMatchInfoSQL(@Param("matchConditionDTO") MatchConditionDTO matchConditionDTO, Page<MatchDO> page);
    public static class createSQL{
        public static String queryMatchInfoSQL(@Param("matchConditionDTO") MatchConditionDTO matchConditionDTO){
            return new SQL(){
                {
                    StringBuffer sql = new StringBuffer();
                    //sql.append(" a.objuid,a.returnpgh,a.jx,a.equipid,a.equipname,a.category,a.materialmark,a.materialstd,a.materialfmt, ");
                    //sql.append(" a.materialdwg,a.unit,a.partid,a.part_name,a.purpose,a.pickman,a.quotanum,a.storekeeper,a.worksection, ");
                    //sql.append(" a.team,a.prepaper,a.issuedate,a.barcode,a.registerid,a.lupihao,a.location,a.stock,a.jcamount, ");
                    //sql.append(" a.issuenum,a.accountprice,a.sfamount,a.lrreturnpgh from mes_account_match a where 1 = 1 ");
                    sql.append(" a.objuid, a.unitname, a.internalcode, a.outorderid, a.accountid, a.outmethod, a.accountmonth,");
                    sql.append(" a.returnpgh, a.jx, a.equipid, a.equipname, a.category, a.materialmark, a.materialstd, a.materialfmt,");
                    sql.append(" a.materialdwg, a.unit, a.partid, a.part_name, a.purpose, a.quotanum, a.worksection, a.team,");
                    sql.append(" a.issuedate, a.barcode, a.registerid, a.lupihao, a.location, a.stock, a.jcamount, a.issuenum,");
                    sql.append(" a.accountprice, a.sfamount, a.lrreturnpgh, a.checkmark, a.checksign, a.checkdate, a.aduitmark,");
                    sql.append(" a.aduitsign, a.aduitdate, a.createid, a.createtime, a.factory_id, b.eid as pickman,");
                    sql.append(" c.name as storekeeper, d.name as prepaper from ");
                    sql.append(" (select a.objuid, a.unitname, a.internalcode, a.outorderid, a.accountid, a.outmethod, a.accountmonth,");
                    sql.append(" a.returnpgh, a.jx, a.equipid, a.equipname, a.category, a.materialmark, a.materialstd, a.materialfmt,");
                    sql.append(" a.materialdwg, a.unit, a.partid, a.part_name, a.purpose, a.quotanum, a.worksection, a.team,");
                    sql.append(" a.issuedate, a.barcode, a.registerid, a.lupihao, a.location, a.stock, a.jcamount, a.issuenum,");
                    sql.append(" a.accountprice, a.sfamount, a.lrreturnpgh, a.checkmark, a.checksign, a.checkdate, a.aduitmark,");
                    sql.append(" a.aduitsign, a.aduitdate, a.createid, a.createtime, a.factory_id, a.pickman, a.storekeeper, a.prepaper");
                    sql.append(" from mes_account_match a where a.factory_id = #{matchConditionDTO.factoryId, jdbcType=VARCHAR}) a");
                    sql.append(" left join (select b.objuid, b.eid from stf_employee b) b on a.pickman = b.objuid");
                    sql.append(" left join (select c.objuid, c.name from stf_employee c) c on a.storekeeper = c.objuid");
                    sql.append(" left join (select d.objuid, d.name from stf_employee d) d on a.prepaper = d.objuid");
                    sql.append(" where 1 = 1 ");
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getReturnPgh())){//派工号
                        sql.append(" and upper(a.returnpgh) = trim(upper(#{matchConditionDTO.returnPgh, jdbcType=VARCHAR})) ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getJx())){//产品明细 派工号对应的机型
                        sql.append(" and upper(a.jx) = trim(upper(#{matchConditionDTO.jx, jdbcType=VARCHAR})) ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getEquipId())){//器材编码
                        sql.append(" and a.equipid = #{matchConditionDTO.equipId, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getEquipName())){//器材名称
                        sql.append(" and a.equipName = #{matchConditionDTO.equipName, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getCategory())){//类别
                        sql.append(" and a.category = #{matchConditionDTO.category, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getMaterialMark())){//牌号
                        sql.append(" and a.materialmark = #{matchConditionDTO.materialMark, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getMaterialStd())){//技术标准
                        sql.append(" and a.materialstd = #{matchConditionDTO.materialStd, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getMaterialFmt())){//规格
                        sql.append(" and a.materialfmt = #{matchConditionDTO.materialFmt, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getMaterialDwg())){//材料图号
                        sql.append(" and a.materialdwg = #{matchConditionDTO.materialDwg, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getPartId())){//零件图号
                        sql.append(" and upper(a.partid) = trim(upper(#{matchConditionDTO.partId, jdbcType=VARCHAR})) ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getPartName())){//零件名称
                        sql.append(" and a.part_name = #{matchConditionDTO.part_name, jdbcType=VARCHAR} ");
                    }
                    if (matchConditionDTO.getIssueDate() != null){//发出日期时间段
                        sql.append(" and a.issuedate >= #{matchConditionDTO.issueDate, jdbcType=DATE} ");
                    }
                    if (matchConditionDTO.getIssueDate2() != null){
                        sql.append(" and a.issuedate <= #{matchConditionDTO.issueDate2, jdbcType=DATE} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getBarCode())){//条码
                        sql.append(" and a.barcode = #{matchConditionDTO.barCode, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getRegisterId())){//到货登记号
                        sql.append(" and a.registerid = #{matchConditionDTO.registerId, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getLrReturnPgh())){//领入派工号
                        sql.append(" and a.lrreturnpgh = #{matchConditionDTO.lrReturnPgh, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getIsMatch())){
                        if (matchConditionDTO.getIsMatch().equals("0")) {
                            sql.append(" and a.equipid is null");
                        } else {
                            sql.append(" and a.equipid is not null");
                        }
                    }
                    sql.append(" order by a.jx");
                    SELECT(sql.toString());
                }
            }.toString();
        }
    }
    //匹配二级成本核算系统的数据
//    @DS("db2")
//    @Select(" select 器材编码 as equipid, 材料名称 as equipname, 类别 as category, 材料牌号 as materialMark, " +
//            "技术标准 as materialStd, 材料规格 as materialFmt, 计量单位 as unit, 用途项目 as purpose, " +
//            "到货登记号 as registerId, LPH as lupihao, 存放库位 as location, 月初库存 as stock, 结存金额 as jcAmount, " +
//            "核算价格 as accountPrice, 发出金额 as sfAmount, 派工号 as lrReturnPgh from dbo.标准件中心库存 " +
//            "  where 材料牌号 = #{matchInfoDTO.materialMark, jdbcType=VARCHAR} " +
//            "  and 技术标准 = #{matchInfoDTO.materialStd, jdbcType=VARCHAR} " +
//            "  and 材料规格 = #{matchInfoDTO.materialFmt, jdbcType=VARCHAR} " +
//            "  and 计量单位 = #{matchInfoDTO.unit, jdbcType=VARCHAR} " +
//            "  and LPH = #{matchInfoDTO.lupihao, jdbcType=VARCHAR} " +
//            "  and 单位代码 = '1400'")
//    List<SCAccountingDTO> matchSCA(@Param("matchInfoDTO") MatchInfoDTO matchInfoDTO);
//    //获取匹配数据数量
//    @DS("db2")
//    @Select(" select count(*) from dbo.标准件中心库存 " +
//            "  where 材料牌号 = #{matchInfoDTO.materialMark, jdbcType=VARCHAR} " +
//            "  and 技术标准 = #{matchInfoDTO.materialStd, jdbcType=VARCHAR} " +
//            "  and 材料规格 = #{matchInfoDTO.materialFmt, jdbcType=VARCHAR} " +
//            "  and 计量单位 = #{matchInfoDTO.unit, jdbcType=VARCHAR} " +
//            "  and LPH = #{matchInfoDTO.lupihao, jdbcType=VARCHAR} " +
//            "  and 单位代码 = '1400'")
//    int matchNum(@Param("matchInfoDTO") MatchInfoDTO matchInfoDTO);
    //按主键获取二级成本核算数据
    //@Select(" select * from second_cost_accounting a where a.objuid = #{objuid, jdbcType=VARCHAR} ")
    //SCAccountingDTO matchSCAById(@Param("objuid") String objuid);

    //查询对应行的所有信息
    @Select("select * from mes_account_match a where a.objuid = #{objuid, jdbcType=VARCHAR}")
    MatchDO info(@Param("objuid")String objuid);

    //将单条匹配数据插入对应行内
    @Update(" update mes_account_match a set " +
            "   a.equipid = #{scAccountingDTO.equipId, jdbcType=VARCHAR}, " +
            "   a.equipname = #{scAccountingDTO.equipName, jdbcType=VARCHAR}, " +
            "   a.category = #{scAccountingDTO.category, jdbcType=VARCHAR}, " +
            "   a.purpose = #{scAccountingDTO.purpose, jdbcType=VARCHAR}, " +
            "   a.registerid = #{scAccountingDTO.registerId, jdbcType=VARCHAR}, " +
            "   a.location = #{scAccountingDTO.location, jdbcType=VARCHAR}, " +
            "   a.stock = #{scAccountingDTO.stock, jdbcType=VARCHAR}, " +
            "   a.jcamount = #{scAccountingDTO.jcAmount, jdbcType=DOUBLE}, " +
            "   a.accountprice = #{scAccountingDTO.accountPrice, jdbcType=DOUBLE}, " +
            "   a.sfamount = #{scAccountingDTO.sfAmount, jdbcType=DOUBLE}, " +
            "   a.lrreturnpgh = #{scAccountingDTO.lrReturnPgh, jdbcType=VARCHAR}, " +
            "   a.createid = #{scAccountingDTO.createId, jdbcType=VARCHAR}, " +
            "   a.createtime = sysdate, " +
            "   a.issuenum = #{scAccountingDTO.bangdingnum, jdbcType=DOUBLE}," +
            "   a.materialstd = #{scAccountingDTO.materialStd, jdbcType=VARCHAR}," +
            "   a.unit = #{scAccountingDTO.unit, jdbcType=VARCHAR}," +
            "   a.worksection = #{employee.gongduan, jdbcType=VARCHAR}," +
            "   a.team = #{employee.banzu, jdbcType=VARCHAR}," +
            "   a.accountid = #{scAccountingDTO.zhangye, jdbcType=VARCHAR}" +
            " where a.objuid = #{scAccountingDTO.mesObjuid, jdbcType=VARCHAR} ")
    int updateMatch(@Param("scAccountingDTO") SCAccountingDTO scAccountingDTO, @Param("employee") EmployeeFromSCADTO employee);

    //生成多条匹配数据
    @Insert("insert into mes_account_match(unitname, internalcode, outorderid, accountid," +
            " accountmonth, returnpgh, jx, equipid, equipname, category, materialmark," +
            " materialstd, materialfmt, materialdwg, unit, partid, part_name, purpose," +
            " pickman, quotanum, storekeeper, prepaper, issuedate," +
            " barcode, registerid, lupihao, location, stock, jcamount, issuenum, accountprice," +
            " sfamount, lrreturnpgh, checkmark, checksign, checkdate, aduitmark, aduitsign," +
            " aduitdate, createid, createtime, factory_id, worksection, team) values (" +
            " #{matchDO.unitName},#{matchDO.internalCode},#{matchDO.outOrderId},#{scAccountingDTO.zhangye},#{matchDO.accountMonth}," +
            " #{matchDO.returnPgh},#{matchDO.jx},#{scAccountingDTO.equipId},#{scAccountingDTO.equipName},#{scAccountingDTO.category}," +
            " #{matchDO.materialMark},#{matchDO.materialStd},#{matchDO.materialFmt},#{matchDO.materialDwg},#{scAccountingDTO.unit}," +
            " #{matchDO.partId},#{matchDO.partName},#{scAccountingDTO.purpose},#{matchDO.pickMan},#{matchDO.quotaNum}," +
            " #{matchDO.storeKeeper},#{matchDO.prePaper},#{matchDO.issueDate}," +
            " #{matchDO.barCode},#{scAccountingDTO.registerId},#{matchDO.lupihao},#{scAccountingDTO.location},#{scAccountingDTO.stock}," +
            " #{scAccountingDTO.jcAmount},#{scAccountingDTO.bangdingnum},#{scAccountingDTO.accountPrice},#{scAccountingDTO.sfAmount},#{scAccountingDTO.lrReturnPgh}," +
            " #{matchDO.checkMark},#{matchDO.checkSign},#{matchDO.checkDate},#{matchDO.aduitMark},#{matchDO.aduitSign}," +
            " #{matchDO.aduitDate},#{scAccountingDTO.createId},sysdate,#{matchDO.factoryId},#{employee.gongduan},#{employee.banzu})")
    int insertMatch(@Param("scAccountingDTO") SCAccountingDTO scAccountingDTO, @Param("matchDO")MatchDO matchDO, @Param("employee") EmployeeFromSCADTO employee);

    @Insert("insert into mes_account_match(unitname, internalcode, outorderid, accountid," +
            " accountmonth, returnpgh, jx, materialmark," +
            " materialstd, materialfmt, materialdwg, unit, partid, part_name," +
            " pickman, quotanum, storekeeper, worksection, team, prepaper, issuedate," +
            " barcode, lupihao,issuenum, " +
            " checkmark, checksign, checkdate, aduitmark, aduitsign," +
            " aduitdate, factory_id) values (" +
            " #{matchDO.unitName},#{matchDO.internalCode},#{matchDO.outOrderId},#{matchDO.accountId},#{matchDO.accountMonth}," +
            " #{matchDO.returnPgh},#{matchDO.jx}," +
            " #{matchDO.materialMark},#{matchDO.materialStd},#{matchDO.materialFmt},#{matchDO.materialDwg},#{matchDO.unit}," +
            " #{matchDO.partId},#{matchDO.partName},#{matchDO.pickMan},#{matchDO.quotaNum}," +
            " #{matchDO.storeKeeper},#{matchDO.workSection},#{matchDO.team},#{matchDO.prePaper},#{matchDO.issueDate}," +
            " #{matchDO.barCode},#{matchDO.lupihao},#{matchDO.issueNum}," +
            " #{matchDO.checkMark},#{matchDO.checkSign},#{matchDO.checkDate},#{matchDO.aduitMark},#{matchDO.aduitSign}," +
            " #{matchDO.aduitDate},#{matchDO.factoryId})")
    int weiwanquanMatch(@Param("matchDO")MatchDO matchDO);

    //存入已匹配的二级成本核算系统信息
    //@Insert("insert into mes_account_match_complete (registerid,createuid,factory_id) values \n" +
    //        "(#{scAccountingDTO.registerid, jdbcType=VARCHAR}, #{uid, jdbcType=VARCHAR}, #{factoryId, jdbcType=VARCHAR})")
    @Insert("insert into mes_account_match_complete (registerid,createuid,materialstd,materialfmt,unit,materialmark,factory_id, " +
            "stock, amount, createtime)" +
            " values (" +
            "#{mesAccountMatchCompleteDTO.registerid, jdbcType=VARCHAR}, #{mesAccountMatchCompleteDTO.createuid, jdbcType=VARCHAR}, " +
            "#{mesAccountMatchCompleteDTO.materialstd, jdbcType=VARCHAR}, #{mesAccountMatchCompleteDTO.materialfmt, jdbcType=VARCHAR}, " +
            "upper(#{mesAccountMatchCompleteDTO.unit, jdbcType=VARCHAR}), #{mesAccountMatchCompleteDTO.materialmark, jdbcType=VARCHAR}, " +
            "#{mesAccountMatchCompleteDTO.factoryId, jdbcType=VARCHAR}, #{mesAccountMatchCompleteDTO.stock, jdbcType=DOUBLE}," +
            "#{mesAccountMatchCompleteDTO.amount, jdbcType=VARCHAR}, sysdate)")
    void insertComplete(@Param("mesAccountMatchCompleteDTO") MesAccountMatchCompleteDTO mesAccountMatchCompleteDTO);

    //查询已经匹配过的二级成本核算系统数据
    //@Select(" select * from mes_account_match_complete a" +
    //        "  where a.materialmark = #{matchInfoDTO.materialMark, jdbcType=VARCHAR} " +
    //        "  and a.materialStd = #{matchInfoDTO.materialStd, jdbcType=VARCHAR} " +
    //        "  and a.materialFmt = #{matchInfoDTO.materialFmt, jdbcType=VARCHAR} " +
    //        "  and a.unit = upper(#{matchInfoDTO.unit, jdbcType=VARCHAR})" +
    //        "  and a.factory_id = #{matchInfoDTO.factoryId, jdbcType=VARCHAR}")
    //@Select("select a.registerid, min(a.stock) as stock from mes_account_match_complete a" +
    //        " where a.registerid in (#{matchInfoDTO.registerId})" +
    //        " and a.factory_id = #{matchInfoDTO.factoryId, jdbcType=VARCHAR}" +
    //        " group by a.registerid")
    @SelectProvider(type = createSQL2.class, method = "completeSQL")
    List<MesAccountMatchCompleteDTO> complete(@Param("matchInfoDTO") MatchInfoDTO matchInfoDTO);
    public static class createSQL2{
        public static String completeSQL(@Param("matchInfoDTO") MatchInfoDTO matchInfoDTO){
            return new SQL(){
                {
                    StringBuffer sql = new StringBuffer();
                    sql.append(" a.registerid,a.stock,a.amount from");
                    sql.append(" (select a.registerid,a.stock,a.amount,");
                    sql.append(" row_number() over(partition by a.registerid order by a.createtime desc) as aa");
                    sql.append(" from mes_account_match_complete a");
                    sql.append(" where a.registerid in (");
                    sql.append(matchInfoDTO.getRegisterId());
                    sql.append(")");
                    sql.append(" and a.factory_id = #{matchInfoDTO.factoryId, jdbcType=VARCHAR}) a");
                    sql.append(" where a.aa = 1");
                    SELECT(sql.toString());
                }
            }.toString();
        }
    }

    // 获取导出信息总数
    @SelectProvider(type = createSQLs.class, method = "countExportSQLs")
    int countExport(@Param("matchConditionDTO") MatchConditionDTO matchConditionDTO);
    public static class createSQLs{
        public static String countExportSQLs(@Param("matchConditionDTO") MatchConditionDTO matchConditionDTO){
            return new SQL(){
                {
                    StringBuffer sql = new StringBuffer();
                    //sql.append(" a.objuid,a.returnpgh,a.jx,a.equipid,a.equipname,a.category,a.materialmark,a.materialstd,a.materialfmt, ");
                    //sql.append(" a.materialdwg,a.unit,a.partid,a.part_name,a.purpose,a.pickman,a.quotanum,a.storekeeper,a.worksection, ");
                    //sql.append(" a.team,a.prepaper,a.issuedate,a.barcode,a.registerid,a.lupihao,a.location,a.stock,a.jcamount, ");
                    //sql.append(" a.issuenum,a.accountprice,a.sfamount,a.lrreturnpgh from mes_account_match a where 1 = 1 ");
                    sql.append(" count(*) from mes_account_match a");
                    sql.append(" where 1 = 1 ");
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getObjuid())){//主键数组
                        sql.append(" and a.objuid in ( ");
                        sql.append(matchConditionDTO.getObjuid());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getReturnPgh())){//派工号
                        sql.append(" and a.returnpgh = #{matchConditionDTO.returnPgh, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getJx())){//产品明细 派工号对应的机型
                        sql.append(" and a.jx = #{matchConditionDTO.jx, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getMaterialMark())){//牌号
                        sql.append(" and a.materialmark = #{matchConditionDTO.materialMark, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getPartId())){//零件图号
                        sql.append(" and a.partid = #{matchConditionDTO.partId, jdbcType=VARCHAR} ");
                    }
                    if (matchConditionDTO.getIssueDate() != null){//发出日期时间段
                        sql.append(" and a.issuedate >= #{matchConditionDTO.issueDate, jdbcType=DATE} ");
                    }
                    if (matchConditionDTO.getIssueDate2() != null){
                        sql.append(" and a.issuedate <= #{matchConditionDTO.issueDate2, jdbcType=DATE} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getBarCode())){//条码
                        sql.append(" and a.barcode = #{matchConditionDTO.barCode, jdbcType=VARCHAR} ");
                    }
                    SELECT(sql.toString());
                    //测试
                    System.out.println(">>countExportSQLs:" + sql.toString());
                }
            }.toString();
        }
    }

    //导出查询
    @SelectProvider(type = createSQL4.class, method = "exportMatchInfoSQL")
    List<MatchDO> exportMatchInfo(@Param("matchConditionDTO") MatchConditionDTO matchConditionDTO, Page<MatchDO> page);
    public static class createSQL4{
        public static String exportMatchInfoSQL(@Param("matchConditionDTO") MatchConditionDTO matchConditionDTO){
            return new SQL(){
                {
                    StringBuffer sql = new StringBuffer();
                    sql.append(" a.objuid, a.unitname, a.internalcode, a.outorderid, a.accountid, a.outmethod, a.accountmonth,");
                    sql.append(" a.returnpgh, a.jx, a.equipid, a.equipname, a.category, a.materialmark, a.materialstd, a.materialfmt,");
                    sql.append(" a.materialdwg, a.unit, a.partid, a.part_name, a.purpose, a.quotanum, a.worksection, a.team,");
                    sql.append(" to_char(a.issuedate, 'yyyy-mm-dd'), a.registerid, a.location, a.stock, a.jcamount, a.issuenum,");
                    sql.append(" a.accountprice, a.sfamount, a.lrreturnpgh, a.checkmark, a.checksign, a.checkdate, a.aduitmark,");
                    sql.append(" a.aduitsign, a.aduitdate, a.createid, a.createtime, a.factory_id, b.eid as pickman,");
                    sql.append(" c.name as storekeeper, d.name as prepaper from ");
                    sql.append(" (select a.objuid, a.unitname, a.internalcode, a.outorderid, a.accountid, a.outmethod, a.accountmonth,");
                    sql.append(" a.returnpgh, a.jx, a.equipid, a.equipname, a.category, a.materialmark, a.materialstd, a.materialfmt,");
                    sql.append(" a.materialdwg, a.unit, a.partid, a.part_name, a.purpose, a.quotanum, a.worksection, a.team,");
                    sql.append(" a.issuedate, a.barcode, a.registerid, a.lupihao, a.location, a.stock, a.jcamount, a.issuenum,");
                    sql.append(" a.accountprice, a.sfamount, a.lrreturnpgh, a.checkmark, a.checksign, a.checkdate, a.aduitmark,");
                    sql.append(" a.aduitsign, a.aduitdate, a.createid, a.createtime, a.factory_id, a.pickman, a.storekeeper, a.prepaper");
                    sql.append(" from mes_account_match a where a.factory_id = #{matchConditionDTO.factoryId, jdbcType=VARCHAR}) a");
                    sql.append(" left join (select b.objuid, b.eid from stf_employee b) b on a.pickman = b.objuid");
                    sql.append(" left join (select c.objuid, c.name from stf_employee c) c on a.storekeeper = c.objuid");
                    sql.append(" left join (select d.objuid, d.name from stf_employee d) d on a.prepaper = d.objuid");
                    sql.append(" where 1 = 1 ");
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getObjuid())){//主键数组
                        sql.append(" and a.objuid in ( ");
                        sql.append(matchConditionDTO.getObjuid());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getReturnPgh())){//派工号
                        sql.append(" and upper(a.returnpgh) = trim(upper(#{matchConditionDTO.returnPgh, jdbcType=VARCHAR})) ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getJx())){//产品明细 派工号对应的机型
                        sql.append(" and upper(a.jx) = trim(upper(#{matchConditionDTO.jx, jdbcType=VARCHAR})) ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getEquipId())){//器材编码
                        sql.append(" and a.equipid = #{matchConditionDTO.equipId, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getEquipName())){//器材名称
                        sql.append(" and a.equipName = #{matchConditionDTO.equipName, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getCategory())){//类别
                        sql.append(" and a.category = #{matchConditionDTO.category, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getMaterialMark())){//牌号
                        sql.append(" and a.materialmark = #{matchConditionDTO.materialMark, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getMaterialStd())){//技术标准
                        sql.append(" and a.materialstd = #{matchConditionDTO.materialStd, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getMaterialFmt())){//规格
                        sql.append(" and a.materialfmt = #{matchConditionDTO.materialFmt, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getMaterialDwg())){//材料图号
                        sql.append(" and a.materialdwg = #{matchConditionDTO.materialDwg, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getPartId())){//零件图号
                        sql.append(" and upper(a.partid) = trim(upper(#{matchConditionDTO.partId, jdbcType=VARCHAR})) ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getPartName())){//零件名称
                        sql.append(" and a.part_name = #{matchConditionDTO.part_name, jdbcType=VARCHAR} ");
                    }
                    if (matchConditionDTO.getIssueDate() != null){//发出日期时间段
                        sql.append(" and a.issuedate >= #{matchConditionDTO.issueDate, jdbcType=DATE} ");
                    }
                    if (matchConditionDTO.getIssueDate2() != null){
                        sql.append(" and a.issuedate <= #{matchConditionDTO.issueDate2, jdbcType=DATE} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getBarCode())){//条码
                        sql.append(" and a.barcode = #{matchConditionDTO.barCode, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getRegisterId())){//到货登记号
                        sql.append(" and a.registerid = #{matchConditionDTO.registerId, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getLrReturnPgh())){//领入派工号
                        sql.append(" and a.lrreturnpgh = #{matchConditionDTO.lrReturnPgh, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(matchConditionDTO.getIsMatch())){
                        if (matchConditionDTO.getIsMatch().equals("0")) {
                            sql.append(" and a.equipid is null");
                        } else {
                            sql.append(" and a.equipid is not null");
                        }
                    }
                    sql.append(" order by a.jx");
                    SELECT(sql.toString());
                }
            }.toString();
        }
    }

    /**
     * 打印查询
     * @param objuidList
     * @return
     */
    @SelectProvider(type = createSQL3.class, method = "queryPrintSQLs")
    List<MesAccountMatchPrintDTO> queryPrint(String objuidList);
    public static class createSQL3 {
        public static String queryPrintSQLs(String objuidList) {
            return new SQL() {
                {
                    StringBuffer sql = new StringBuffer();
                    sql.append(" a.*, b.name as pickManName, c.name as storeKeeperName from");
                    sql.append(" (select * from mes_account_match a where a.objuid in (");
                    sql.append(objuidList);
                    sql.append(")) a");
                    sql.append(" left join (select b.objuid, b.name from stf_employee b) b on a.pickman = b.objuid");
                    sql.append(" left join (select c.objuid, c.name from stf_employee c) c on a.storekeeper = c.objuid");
                    SELECT(sql.toString());
                }
            }.toString();
        }
    }

    /**
     * 生成器材使用单单号用查询
     * @return
     */
    @Select("select count(*) from mes_account_match a where a.barcode_print is not null")
    int queryPrintCount();

    /**
     * 存入器材使用单单号
     * @param objuid
     * @param barcodePrint
     */
    @Update("update mes_account_match a set" +
            " a.barcode_print = #{barcodePrint, jdbcType=VARCHAR}" +
            " where a.objuid = #{objuid, jdbcType=VARCHAR}")
    void updatebarcodePrint(@Param("objuid")String objuid, @Param("barcodePrint")String barcodePrint);
}